
if exists (select 1 from sysobjects where name = 'get_personcanceldinschema' and type = 'P')
begin
   drop procedure get_personcanceldinschema
   print 'Procedure: get_personcanceldinschema deleted ...'
end
go
create procedure get_personcanceldinschema(
  @schemaid int = 1
)
as
begin
  set nocount on

  select p.PrsId,
         a.AbteilungID,
         p.Vorname,
         p.Nachname,
         a.Name
    from Abteilung a
    left outer join PersonalAbteilung pa
      on a.AbteilungID = pa.AbteilungID
    left outer join Person p
     on p.PrsId = pa.PrsId
    and p.PersonTyp = 1
   left outer join PlanSchema ps
     on ps.AbteilungID = a.AbteilungID
    and ps.PrsID = p.PrsId
  inner join PersonAnstellung pan on pan.PrsID = ps.PrsID 
  where pan.Bis <= getdate()
    and ps.SchemaID = @schemaid
and pa.Bis = (select max(pa2.Bis) from PersonalAbteilung pa2 where pa2.PrsId = p.PrsId and a.AbteilungID = pa2.AbteilungID)

end
go
print 'Procedure: get_personcanceldinschema done ...'
go

grant exec on get_personcanceldinschema to prsadmins with grant option
go
grant exec on get_personcanceldinschema to prsusers
go

